/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package monitoring.dbpersistence;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import monitoring.beans.database.*;
import monitoring.beans.util.ImportMessage;
import monitoring.dbpersistence.util.DBConnection;
import monitoring.dbpersistence.util.SQLTreatement;
import monitoring.dbpersistence.util.Validation;

/**
 *
 * @author allankv
 */
public class FillDependency {

    private DBConnection conn;
    private Validation v;
    private SQLTreatement t;
    private List<ImportMessage> msgs;
    private String sqlSelect = "select id_table_ from _table_ where _table_='_value_';";
    private String sqlInsert = "insert into _table_(_table_) values ('_value_') RETURNING id_table_;";
    private Map<String, Integer> collectorCache = new HashMap<String, Integer>();
    private Map<String, Integer> institutionCodeCache = new HashMap<String, Integer>();
    private Map<String, Integer> collectionCodeCache = new HashMap<String, Integer>();
    private Map<String, Integer> colorPanTrapCache = new HashMap<String, Integer>();
    private Map<String, Integer> cultivarCache = new HashMap<String, Integer>();
    private Map<String, Integer> cultureCache = new HashMap<String, Integer>();
    private Map<String, Integer> denominationCache = new HashMap<String, Integer>();
    private Map<String, Integer> digitizerCache = new HashMap<String, Integer>();
    private Map<String, Integer> predominantBiomeCache = new HashMap<String, Integer>();
    private Map<String, Integer> supportTypeCache = new HashMap<String, Integer>();
    private Map<String, Integer> tehchnicalCollectionCache = new HashMap<String, Integer>();
    private Map<String, Integer> surroundingsCultureCache = new HashMap<String, Integer>();
    private Map<String, Integer> surroundingsVegetationCache = new HashMap<String, Integer>();
    private Map<String, Integer> countryCache = new HashMap<String, Integer>();
    private Map<String, Integer> localityCache = new HashMap<String, Integer>();
    private Map<String, Integer> municipalityCache = new HashMap<String, Integer>();
    private Map<String, Integer> stateProvinceCache = new HashMap<String, Integer>();
    private Map<String, Integer> sexCache = new HashMap<String, Integer>();
    private Map<String, Integer> basisOfRecordCache = new HashMap<String, Integer>();
    private Map<String, Integer> familyCache = new HashMap<String, Integer>();
    private Map<String, Integer> genusCache = new HashMap<String, Integer>();
    private Map<String, Integer> orderCache = new HashMap<String, Integer>();
    private Map<String, Integer> scientificNameCache = new HashMap<String, Integer>();
    private Map<String, Integer> speciesNameCache = new HashMap<String, Integer>();
    private Map<String, Integer> subGenusCache = new HashMap<String, Integer>();
    private Map<String, Integer> subSpeciesCache = new HashMap<String, Integer>();
    private Map<String, Integer> subTribeCache = new HashMap<String, Integer>();
    private Map<String, Integer> tribeCache = new HashMap<String, Integer>();

    public MonitoringDB fillDependency(MonitoringDB d) throws SQLException {
        conn = new DBConnection();
        t = new SQLTreatement();
        msgs = new ArrayList<ImportMessage>();

        d.setCollector(fillCollector(d.getCollector()));
        d.setColorPanTrap(fillColorPanTrap(d.getColorPanTrap(), d.getRow()));
        d.setCultivar(fillCultivar(d.getCultivar()));
        d.setCulture(fillCulture(d.getCulture()));
        d.setDenomination(fillDenomination(d.getDenomination()));
        d.setDigitizer(fillDigitizer(d.getDigitizer()));
        d.setPredominantBiome(fillPredominantBiome(d.getPredominantBiome(), d.getRow()));//***
        d.setSupportType(fillSupportType(d.getSupportType(), d.getRow())); //***
        d.setTechnicalCollection(fillTechnicalCollection(d.getTechnicalCollection(), d.getRow()));//***        
        d.setSurroundingsCulture(fillSurroundingsCulture(d.getSurroundingsCulture(), d.getRow()));//***
        d.setSurroundingsVegetation(fillSurroundingsVegetation(d.getSurroundingsVegetation(),d.getRow()));
        d.getLocalityElement().setCountry(fillCountry(d.getLocalityElement().getCountry()));
        d.getLocalityElement().setLocality(fillLocality(d.getLocalityElement().getLocality()));
        d.getLocalityElement().setMunicipality(fillMunicipality(d.getLocalityElement().getMunicipality()));
        d.getLocalityElement().setStateProvince(fillStateProvince(d.getLocalityElement().getStateProvince()));
        d.getOcurrenceElement().setSex(fillSex(d.getOcurrenceElement().getSex(), d.getRow()));
        d.getRecordLevelElement().setBasisOfRecord(fillBasisOfRecord(d.getRecordLevelElement().getBasisOfRecord()));//***
        d.getTaxonomicElement().setFamily(fillFamily(d.getTaxonomicElement().getFamily()));
        d.getTaxonomicElement().setGenus(fillGenus(d.getTaxonomicElement().getGenus()));
        d.getTaxonomicElement().setOrder(fillOrder(d.getTaxonomicElement().getOrder()));
        d.getTaxonomicElement().setScientificName(fillScientificName(d.getTaxonomicElement().getScientificName()));
        d.getTaxonomicElement().setSpeciesName(fillSpeciesName(d.getTaxonomicElement().getSpeciesName()));
        d.getTaxonomicElement().setSubGenus(fillSubGenus(d.getTaxonomicElement().getSubGenus()));
        d.getTaxonomicElement().setSubSpecies(fillSubSpecies(d.getTaxonomicElement().getSubSpecies()));
        d.getTaxonomicElement().setSubTribe(fillSubTribe(d.getTaxonomicElement().getSubTribe()));
        d.getTaxonomicElement().setTribe(fillTribe(d.getTaxonomicElement().getTribe()));
        d.getRecordLevelElement().setInstitutionCode(fillInstitutionCode(d.getRecordLevelElement().getInstitutionCode()));
        d.getRecordLevelElement().setCollectionCode(fillCollectionCode(d.getRecordLevelElement().getCollectionCode()));

        conn.closeConn();
        return d;
    }

    private Collector fillCollector(Collector o) throws SQLException {
        v = new Validation();
        String tbl = "collector";
        Integer id = collectorCache.get(o.getCollector());

        if (id == null && v.isValidOneToN(o.getCollector())) {
            o.setCollector(t.fieldOneToN(o.getCollector()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getCollector());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdCollector(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getCollector());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdCollector(rs.getInt("id" + tbl));
                }
            }
            collectorCache.put(o.getCollector(), o.getIdCollector());
        } else {
            o.setIdCollector(id);
        }
        return o;
    }

    private InstitutionCode fillInstitutionCode(InstitutionCode o) throws SQLException {
        v = new Validation();
        String tbl = "institutioncode";
        Integer id = institutionCodeCache.get(o.getInstitutionCode());

        if (id == null && v.isValidOneToN(o.getInstitutionCode())) {
            o.setInstitutionCode(t.fieldOneToN(o.getInstitutionCode()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getInstitutionCode());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdInstitutionCode(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getInstitutionCode());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdInstitutionCode(rs.getInt("id" + tbl));
                }
            }
            institutionCodeCache.put(o.getInstitutionCode(), o.getIdInstitutionCode());
        } else {
            o.setIdInstitutionCode(id);
        }
        return o;
    }

    private CollectionCode fillCollectionCode(CollectionCode o) throws SQLException {
        v = new Validation();
        String tbl = "collectioncode";
        Integer id = collectionCodeCache.get(o.getCollectionCode());

        if (id == null && v.isValidOneToN(o.getCollectionCode())) {
            o.setCollectionCode(t.fieldOneToN(o.getCollectionCode()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getCollectionCode());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdCollectionCode(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getCollectionCode());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdCollectionCode(rs.getInt("id" + tbl));
                }
            }
            collectionCodeCache.put(o.getCollectionCode(), o.getIdCollectionCode());
        } else {
            o.setIdCollectionCode(id);
        }
        return o;
    }

    private ColorPanTrap fillColorPanTrap(ColorPanTrap o, int row) throws SQLException {
        v = new Validation();
        String tbl = "colorpantrap";
        Integer id = colorPanTrapCache.get(o.getColorPanTrap());

        if (id == null && v.isValidOneToN(o.getColorPanTrap())) {
            if (v.isValidColorPanTrap(o.getColorPanTrap())) {
                o.setColorPanTrap(t.fieldOneToN(o.getColorPanTrap()));
                String sql = sqlSelect.replaceAll("_table_", tbl);
                sql = sql.replaceFirst("_value_", o.getColorPanTrap());
                ResultSet rs = conn.executeSQL(sql, true);
                if (rs.next()) {
                    o.setIdColorPanTrap(rs.getInt("id" + tbl));
                } else {
                    sql = sqlInsert.replaceAll("_table_", tbl);
                    sql = sql.replaceAll("_value_", o.getColorPanTrap());
                    rs = conn.executeSQL(sql, true);
                    while (rs.next()) {
                        o.setIdColorPanTrap(rs.getInt("id" + tbl));
                    }
                }
                colorPanTrapCache.put(o.getColorPanTrap(), o.getIdColorPanTrap());
            } else {
                v.getMsg().setRow(row);
                msgs.add(v.getMsg());
            }
        } else {
            o.setIdColorPanTrap(id);
        }
        return o;
    }

    private Cultivar fillCultivar(Cultivar o) throws SQLException {
        v = new Validation();
        String tbl = "cultivar";
        Integer id = cultivarCache.get(o.getCultivar());

        if (id == null && v.isValidOneToN(o.getCultivar())) {
            o.setCultivar(t.fieldOneToN(o.getCultivar()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getCultivar());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdCultivar(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getCultivar());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdCultivar(rs.getInt("id" + tbl));
                }
            }
            cultivarCache.put(o.getCultivar(), o.getIdCultivar());
        } else {
            o.setIdCultivar(id);
        }
        return o;
    }

    private Culture fillCulture(Culture o) throws SQLException {
        v = new Validation();
        String tbl = "culture";
        Integer id = cultureCache.get(o.getCulture());

        if (id == null && v.isValidOneToN(o.getCulture())) {
            o.setCulture(t.fieldOneToN(o.getCulture()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getCulture());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdCulture(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getCulture());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdCulture(rs.getInt("id" + tbl));
                }
            }
            cultureCache.put(o.getCulture(), o.getIdCulture());
        } else {
            o.setIdCulture(id);
        }
        return o;
    }

    private Denomination fillDenomination(Denomination o) throws SQLException {
        v = new Validation();
        String tbl = "denomination";
        Integer id = denominationCache.get(o.getDenomination());

        if (id == null && v.isValidOneToN(o.getDenomination())) {
            o.setDenomination(t.fieldOneToN(o.getDenomination()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getDenomination());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdDenomination(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getDenomination());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdDenomination(rs.getInt("id" + tbl));
                }
            }
            denominationCache.put(o.getDenomination(), o.getIdDenomination());
        } else {
            o.setIdDenomination(id);
        }
        return o;
    }

    private Digitizer fillDigitizer(Digitizer o) throws SQLException {
        v = new Validation();
        String tbl = "digitizer";
        Integer id = digitizerCache.get(o.getDigitizer());

        if (id == null && v.isValidOneToN(o.getDigitizer())) {
            o.setDigitizer(t.fieldOneToN(o.getDigitizer()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getDigitizer());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdDigitizer(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getDigitizer());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdDigitizer(rs.getInt("id" + tbl));
                }
            }
            digitizerCache.put(o.getDigitizer(), o.getIdDigitizer());
        } else {
            o.setIdDigitizer(id);
        }
        return o;
    }

    private PredominantBiome fillPredominantBiome(PredominantBiome o, int row) throws SQLException {
        v = new Validation();
        String tbl = "predominantbiome";
        Integer id = predominantBiomeCache.get(o.getPredominantBiome());

        if (id == null && v.isValidOneToN(o.getPredominantBiome())) {
            if (v.isValidPredominantBiome(o.getPredominantBiome())) {
                o.setPredominantBiome(t.fieldOneToN(o.getPredominantBiome()));
                String sql = sqlSelect.replaceAll("_table_", tbl);
                sql = sql.replaceFirst("_value_", o.getPredominantBiome());
                ResultSet rs = conn.executeSQL(sql, true);
                if (rs.next()) {
                    o.setIdPredominantBiome(rs.getInt("id" + tbl));
                } else {
                    sql = sqlInsert.replaceAll("_table_", tbl);
                    sql = sql.replaceAll("_value_", o.getPredominantBiome());
                    rs = conn.executeSQL(sql, true);
                    while (rs.next()) {
                        o.setIdPredominantBiome(rs.getInt("id" + tbl));
                    }
                }
                predominantBiomeCache.put(o.getPredominantBiome(), o.getIdPredominantBiome());
            } else {
                v.getMsg().setRow(row);
                msgs.add(v.getMsg());
            }
        } else {
            o.setIdPredominantBiome(id);
        }
        return o;
    }

    private SupportType fillSupportType(SupportType o, int row) throws SQLException {
        v = new Validation();
        String tbl = "supporttype";
        Integer id = supportTypeCache.get(o.getSupportType());

        if (id == null && v.isValidOneToN(o.getSupportType())) {
            if (v.isValidSupportType(o.getSupportType())) {
                o.setSupportType(t.fieldOneToN(o.getSupportType()));
                String sql = sqlSelect.replaceAll("_table_", tbl);
                sql = sql.replaceFirst("_value_", o.getSupportType());
                ResultSet rs = conn.executeSQL(sql, true);
                if (rs.next()) {
                    o.setIdSupportType(rs.getInt("id" + tbl));
                } else {
                    sql = sqlInsert.replaceAll("_table_", tbl);
                    sql = sql.replaceAll("_value_", o.getSupportType());
                    rs = conn.executeSQL(sql, true);
                    while (rs.next()) {
                        o.setIdSupportType(rs.getInt("id" + tbl));
                    }
                }
                supportTypeCache.put(o.getSupportType(), o.getIdSupportType());
            } else {
                v.getMsg().setRow(row);
                msgs.add(v.getMsg());
            }
        } else {
            o.setIdSupportType(id);
        }
        return o;
    }

    private Technicalcollection fillTechnicalCollection(Technicalcollection o, int row) throws SQLException {
        v = new Validation();
        String tbl = "technicalcollection";
        Integer id = tehchnicalCollectionCache.get(o.getTechinicalCollection());

        if (id == null && v.isValidOneToN(o.getTechinicalCollection())) {
            o.setTechinicalCollection(t.fieldOneToN(o.getTechinicalCollection()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getTechinicalCollection());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdTechinicalCollection(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getTechinicalCollection());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdTechinicalCollection(rs.getInt("id" + tbl));
                }
            }
            tehchnicalCollectionCache.put(o.getTechinicalCollection(), o.getIdTechinicalCollection());
        } else {
            o.setIdTechinicalCollection(id);
        }
        return o;
    }

    private SurroundingsCulture fillSurroundingsCulture(SurroundingsCulture o, int row) throws SQLException {
        v = new Validation();
        String tbl = "surroundingsculture";
        Integer id = surroundingsCultureCache.get(o.getSurroundingsCulture());

        if (id == null && v.isValidOneToN(o.getSurroundingsCulture())) {
            if (v.isValidSurrondingsCulture(o.getSurroundingsCulture())) {
                o.setSurroundingsCulture(t.fieldOneToN(o.getSurroundingsCulture()));
                String sql = sqlSelect.replaceAll("_table_", tbl);
                sql = sql.replaceFirst("_value_", o.getSurroundingsCulture());
                ResultSet rs = conn.executeSQL(sql, true);
                if (rs.next()) {
                    o.setIdSurroundingsCulture(rs.getInt("id" + tbl));
                } else {
                    sql = sqlInsert.replaceAll("_table_", tbl);
                    sql = sql.replaceAll("_value_", o.getSurroundingsCulture());
                    rs = conn.executeSQL(sql, true);
                    while (rs.next()) {
                        o.setIdSurroundingsCulture(rs.getInt("id" + tbl));
                    }
                }
                surroundingsCultureCache.put(o.getSurroundingsCulture(), o.getIdSurroundingsCulture());
            } else {
                v.getMsg().setRow(row);
                msgs.add(v.getMsg());
            }
        } else {
            o.setIdSurroundingsCulture(id);
        }
        return o;
    }

    private SurroundingsVegetation fillSurroundingsVegetation(SurroundingsVegetation o, Integer row) throws SQLException {
        v = new Validation();
        String tbl = "surroundingsvegetation";
        Integer id = surroundingsVegetationCache.get(o.getSurroundingsVegetation());

        if (id == null&& v.isValidOneToN(o.getSurroundingsVegetation())) {
            if (v.isValidSurrondingsVegetation(o.getSurroundingsVegetation())) {
                o.setSurroundingsVegetation(t.fieldOneToN(o.getSurroundingsVegetation()));
                String sql = sqlSelect.replaceAll("_table_", tbl);
                sql = sql.replaceFirst("_value_", o.getSurroundingsVegetation());
                ResultSet rs = conn.executeSQL(sql, true);
                if (rs.next()) {
                    o.setIdSurroundingsVegetation(rs.getInt("id" + tbl));
                } else {
                    sql = sqlInsert.replaceAll("_table_", tbl);
                    sql = sql.replaceAll("_value_", o.getSurroundingsVegetation());
                    rs = conn.executeSQL(sql, true);
                    while (rs.next()) {
                        o.setIdSurroundingsVegetation(rs.getInt("id" + tbl));
                    }
                }
                surroundingsVegetationCache.put(o.getSurroundingsVegetation(), o.getIdSurroundingsVegetation());
            } else {
                v.getMsg().setRow(row);
                msgs.add(v.getMsg());
            }
        } else {
            o.setIdSurroundingsVegetation(id);
        }
        return o;
    }

    private Country fillCountry(Country o) throws SQLException {
        v = new Validation();
        String tbl = "country";
        Integer id = countryCache.get(o.getCountry());

        if (id == null && v.isValidOneToN(o.getCountry())) {
            o.setCountry(t.fieldOneToN(o.getCountry()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getCountry());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdCountry(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getCountry());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdCountry(rs.getInt("id" + tbl));
                }
            }
            countryCache.put(o.getCountry(), o.getIdCountry());
        } else {
            o.setIdCountry(id);
        }
        return o;
    }

    private Locality fillLocality(Locality o) throws SQLException {
        v = new Validation();
        String tbl = "locality";
        Integer id = localityCache.get(o.getLocality());

        if (id == null && v.isValidOneToN(o.getLocality())) {
            o.setLocality(t.fieldOneToN(o.getLocality()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getLocality());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdLocality(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getLocality());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdLocality(rs.getInt("id" + tbl));
                }
            }
            localityCache.put(o.getLocality(), o.getIdLocality());
        } else {
            o.setIdLocality(id);
        }
        return o;
    }

    private Municipality fillMunicipality(Municipality o) throws SQLException {
        v = new Validation();
        String tbl = "municipality";
        Integer id = municipalityCache.get(o.getMunicipality());

        if (id == null && v.isValidOneToN(o.getMunicipality())) {
            o.setMunicipality(t.fieldOneToN(o.getMunicipality()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getMunicipality());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdMunicipality(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getMunicipality());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdMunicipality(rs.getInt("id" + tbl));
                }
            }
            municipalityCache.put(o.getMunicipality(), o.getIdMunicipality());
        } else {
            o.setIdMunicipality(id);
        }
        return o;
    }

    private StateProvince fillStateProvince(StateProvince o) throws SQLException {
        v = new Validation();
        String tbl = "stateprovince";
        Integer id = stateProvinceCache.get(o.getStateProvince());

        if (id == null && v.isValidOneToN(o.getStateProvince())) {
            o.setStateProvince(t.fieldOneToN(o.getStateProvince()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getStateProvince());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdStateProvince(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getStateProvince());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdStateProvince(rs.getInt("id" + tbl));
                }
            }
            stateProvinceCache.put(o.getStateProvince(), o.getIdStateProvince());
        } else {
            o.setIdStateProvince(id);
        }
        return o;
    }

    private Sex fillSex(Sex o, int row) throws SQLException {
        
        v = new Validation();
        String tbl = "sex";
        Integer id = sexCache.get(o.getSex());

        if (id == null && o.getSex()!=null && o.getSex().length()>0) {
            if (v.isValidSex(o.getSex())) {
                o.setSex(t.fieldOneToN(o.getSex()));
                String sql = sqlSelect.replaceAll("_table_", tbl);
                sql = sql.replaceFirst("_value_", o.getSex());
                ResultSet rs = conn.executeSQL(sql, true);
                if (rs.next()) {
                    o.setIdSex(rs.getInt("id" + tbl));
                } else {
                    sql = sqlInsert.replaceAll("_table_", tbl);
                    sql = sql.replaceAll("_value_", o.getSex());
                    rs = conn.executeSQL(sql, true);
                    while (rs.next()) {
                        o.setIdSex(rs.getInt("id" + tbl));
                    }
                }
                sexCache.put(o.getSex(), o.getIdSex());
            } else {
                v.getMsg().setRow(row);
                msgs.add(v.getMsg());
            }
        } else {
            o.setIdSex(id);
        }
        return o;
    }

    private BasisOfRecord fillBasisOfRecord(BasisOfRecord o) throws SQLException {
        v = new Validation();
        o.setBasisOfRecord("Preserved Specimen");
        String tbl = "basisofrecord";
        Integer id = basisOfRecordCache.get(o.getBasisOfRecord());

        if (id == null && v.isValidOneToN(o.getBasisOfRecord())) {
            o.setBasisOfRecord(t.fieldOneToN(o.getBasisOfRecord()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getBasisOfRecord());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdBasisOfRecord(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getBasisOfRecord());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdBasisOfRecord(rs.getInt("id" + tbl));
                }
            }
            basisOfRecordCache.put(o.getBasisOfRecord(), o.getIdBasisOfRecord());
        } else {
            o.setIdBasisOfRecord(id);
        }
        return o;
    }

    private Family fillFamily(Family o) throws SQLException {
        v = new Validation();
        String tbl = "family";
        Integer id = familyCache.get(o.getFamily());

        if (id == null && v.isValidOneToN(o.getFamily())) {
            o.setFamily(t.fieldOneToN(o.getFamily()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getFamily());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdFamily(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getFamily());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdFamily(rs.getInt("id" + tbl));
                }
            }
            familyCache.put(o.getFamily(), o.getIdFamily());
        } else {
            o.setIdFamily(id);
        }
        return o;
    }

    private Genus fillGenus(Genus o) throws SQLException {
        v = new Validation();
        String tbl = "genus";
        Integer id = genusCache.get(o.getGenus());

        if (id == null && v.isValidOneToN(o.getGenus())) {
            o.setGenus(t.fieldOneToN(o.getGenus()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getGenus());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdGenus(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getGenus());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdGenus(rs.getInt("id" + tbl));
                }
            }
            genusCache.put(o.getGenus(), o.getIdGenus());
        } else {
            o.setIdGenus(id);
        }
        return o;
    }

    private Order fillOrder(Order o) throws SQLException {
        v = new Validation();
        String tbl = "order";
        Integer id = orderCache.get(o.getOrder());

        if (id == null && v.isValidOneToN(o.getOrder())) {
            o.setOrder(t.fieldOneToN(o.getOrder()));
            String sql = sqlSelect.replaceFirst("_table_", tbl);
            sql = sql.replaceAll("_table_", "\"" + tbl + "\"");
            sql = sql.replaceFirst("_value_", o.getOrder());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdOrder(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceFirst("_table_", "\"" + tbl + "\"");
                sql = sql.replaceFirst("_table_", "\"" + tbl + "\"");
                sql = sql.replaceFirst("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getOrder());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdOrder(rs.getInt("id" + tbl));
                }
            }
            orderCache.put(o.getOrder(), o.getIdOrder());
        } else {
            o.setIdOrder(id);
        }
        return o;
    }

    private ScientificName fillScientificName(ScientificName o) throws SQLException {
        v = new Validation();
        String tbl = "scientificname";
        Integer id = scientificNameCache.get(o.getScientificName());

        if (id == null && v.isValidOneToN(o.getScientificName())) {
            o.setScientificName(t.fieldOneToN(o.getScientificName()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getScientificName());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdScientificName(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getScientificName());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdScientificName(rs.getInt("id" + tbl));
                }
            }
            scientificNameCache.put(o.getScientificName(), o.getIdScientificName());
        } else {
            o.setIdScientificName(id);
        }
        return o;
    }

    private SpeciesName fillSpeciesName(SpeciesName o) throws SQLException {
        v = new Validation();
        String tbl = "speciesname";
        Integer id = speciesNameCache.get(o.getSpeciesName());

        if (id == null && v.isValidOneToN(o.getSpeciesName())) {
            o.setSpeciesName(t.fieldOneToN(o.getSpeciesName()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getSpeciesName());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdSpeciesName(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getSpeciesName());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdSpeciesName(rs.getInt("id" + tbl));
                }
            }
            speciesNameCache.put(o.getSpeciesName(), o.getIdSpeciesName());
        } else {
            o.setIdSpeciesName(id);
        }
        return o;
    }

    private SubGenus fillSubGenus(SubGenus o) throws SQLException {
        v = new Validation();
        String tbl = "subgenus";
        Integer id = subGenusCache.get(o.getSubGenus());

        if (id == null && v.isValidOneToN(o.getSubGenus())) {
            o.setSubGenus(t.fieldOneToN(o.getSubGenus()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getSubGenus());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdSubGenus(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getSubGenus());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdSubGenus(rs.getInt("id" + tbl));
                }
            }
            subGenusCache.put(o.getSubGenus(), o.getIdSubGenus());
        } else {
            o.setIdSubGenus(id);
        }
        return o;
    }

    private SubSpecies fillSubSpecies(SubSpecies o) throws SQLException {
        v = new Validation();
        String tbl = "subspecies";
        Integer id = subSpeciesCache.get(o.getSubSpecies());

        if (id == null && v.isValidOneToN(o.getSubSpecies())) {
            o.setSubSpecies(t.fieldOneToN(o.getSubSpecies()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getSubSpecies());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdSubSpecies(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getSubSpecies());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdSubSpecies(rs.getInt("id" + tbl));
                }
            }
            subSpeciesCache.put(o.getSubSpecies(), o.getIdSubSpecies());
        } else {
            o.setIdSubSpecies(id);
        }
        return o;
    }

    private SubTribe fillSubTribe(SubTribe o) throws SQLException {
        v = new Validation();
        String tbl = "subtribe";
        Integer id = subTribeCache.get(o.getSubTribe());

        if (id == null && v.isValidOneToN(o.getSubTribe())) {
            o.setSubTribe(t.fieldOneToN(o.getSubTribe()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getSubTribe());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdSubTribe(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getSubTribe());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdSubTribe(rs.getInt("id" + tbl));
                }
            }
            subTribeCache.put(o.getSubTribe(), o.getIdSubTribe());
        } else {
            o.setIdSubTribe(id);
        }
        return o;
    }

    private Tribe fillTribe(Tribe o) throws SQLException {
        v = new Validation();
        String tbl = "tribe";
        Integer id = tribeCache.get(o.getTribe());

        if (id == null && v.isValidOneToN(o.getTribe())) {
            o.setTribe(t.fieldOneToN(o.getTribe()));
            String sql = sqlSelect.replaceAll("_table_", tbl);
            sql = sql.replaceFirst("_value_", o.getTribe());
            ResultSet rs = conn.executeSQL(sql, true);
            if (rs.next()) {
                o.setIdTribe(rs.getInt("id" + tbl));
            } else {
                sql = sqlInsert.replaceAll("_table_", tbl);
                sql = sql.replaceAll("_value_", o.getTribe());
                rs = conn.executeSQL(sql, true);
                while (rs.next()) {
                    o.setIdTribe(rs.getInt("id" + tbl));
                }
            }
            tribeCache.put(o.getTribe(), o.getIdTribe());
        } else {
            o.setIdTribe(id);
        }
        return o;
    }

    public List<ImportMessage> getMsgs() {
        return msgs;
    }

    public void setMsgs(List<ImportMessage> msgs) {
        this.msgs = msgs;
    }
    
}
